/****** Object:  StoredProcedure [dbo].[spBaoCaoTheKhoByDate]    Script Date: 10/04/2012 14:01:17 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spBaoCaoTheKhoByDate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spBaoCaoTheKhoByDate]
GO

/****** Object:  StoredProcedure [dbo].[spBaoCaoTheKhoByDate]    Script Date: 10/04/2012 14:01:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spBaoCaoTheKhoByDate]
(
	@MaThuoc nvarchar(12),
	@SoLo nvarchar(12),
	@TuNgay datetime,
	@DenNgay datetime
)

WITH ENCRYPTION
AS

SELECT * FROM tbl_Congty
SELECT
	[tbl_ChiTietPhieuNhap].SoLuong As SoLuong,
	tbl_PhieuNhap.SoHD,
	tbl_PhieuNhap.NgayNhapKho as Ngay,
	tbl_NhaCungCap.TenNCC as DienGiai,
	tbl_LuuKho.*,
	[tbl_SanPham].*,
	tbl_DonViTinh.*
FROM
	[tbl_ChiTietPhieuNhap] INNER JOIN tbl_LuuKho ON tbl_LuuKho.MaLuuKho = [tbl_ChiTietPhieuNhap].MaLuuKho
	INNER JOIN tbl_SanPham ON [tbl_SanPham].Mathuoc = tbl_LuuKho.MaThuoc
	INNER JOIN tbl_PhieuNhap ON [tbl_PhieuNhap].SoPN = [tbl_ChiTietPhieuNhap].SoPN
	INNER JOIN tbl_NhaCungCap ON tbl_NhaCungCap.MaNCC = tbl_PhieuNhap.MaNCC
	INNER JOIN tbl_DonViTinh ON [tbl_DonViTinh].MaDonVi = [tbl_SanPham].MaDonVi
WHERE
	[tbl_SanPham].MaThuoc = @MaTHuoc 
	AND tbl_LuuKho.SoLo = @SoLo
	AND ((@TuNgay is null) or (convert(date, @TuNgay) <= convert(date, tbl_PhieuNhap.NgayNhapKho)))
	AND ((@DenNgay is null) or (convert(date, @DenNgay) >= convert(date, tbl_PhieuNhap.NgayNhapKho)))
UNION All
SELECT
	
	(0 - [Tbl_ChiTietPhieuXuat].SoLuong) As SoLuong,
	tbl_PhieuXuat.SoHD,
	tbl_PhieuXuat.NgayXuatKho as Ngay,
	tbl_KhachHang.TenKH as DienGiai,
	tbl_LuuKho.*,
	[tbl_SanPham].*,
	tbl_DonViTinh.*
FROM
	[Tbl_ChiTietPhieuXuat] INNER JOIN tbl_LuuKho ON tbl_LuuKho.MaLuuKho = [Tbl_ChiTietPhieuXuat].MaLuuKho
	INNER JOIN tbl_SanPham ON tbl_SanPham.MaThuoc = tbl_LuuKho.MaThuoc
	INNER JOIN tbl_DonViTinh ON tbl_SanPham.MaDonVi = tbl_DonViTinh.MaDonVi
	INNER JOIN tbl_PhieuXuat ON [tbl_PhieuXuat].SoPX = [Tbl_ChiTietPhieuXuat].SoPX
	INNER JOIN tbl_KhachHang ON tbl_KhachHang.MaKH = tbl_PhieuXuat.MaKH
WHERE
	tbl_LuuKho.MaThuoc = @MaTHuoc 
	AND tbl_LuuKho.SoLo = @SoLo
	AND ((@TuNgay is null) or (convert(date, @TuNgay) <= convert(date, tbl_PhieuXuat.NgayXuatKho)))
	AND ((@DenNgay is null) or (convert(date, @DenNgay) >= convert(date, tbl_PhieuXuat.NgayXuatKho)))
--ORDER BY 3  --Bo sort theo Ngay vi se hien thi gia tri ton kho am. 
              -- Vi du: Nhap kho ngay 1/1/2012 SL 300. Nhap kho ngay 3/1/2012 SL 200
              --        Xuat kho ngay 2/1/2012 SL 400. Neu sort theo Ngay thi cot TonKho co dong se co gia tri -100

GO


